﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace CMS_GYM
{
    public class clsTheDAO
    {
        /// <summary>
        /// Kết nối SQL Server
        /// </summary>
        public CSQLServer sqlServer = new CSQLServer();

        /// <summary>
        /// Lấy DS Thẻ
        /// </summary>
        public DataTable LayBang(decimal KhachHangId)
        {
            string sql = "spGetBangTheTheoKhachHang";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@KhachHangId";
            valueofParameter[0] = KhachHangId;
            DataTable table = sqlServer.readData(sql,ParameterColection, valueofParameter);
            return table;
        }

        public DataTable LayBangTheChuaThu(decimal KhachHangId)
        {
            string sql = "spGetBangTheChuaThuTienTheoKhachHang";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@KhachHangId";
            valueofParameter[0] = KhachHangId;
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }

        private DataTable LayBang(string MaThe)
        {
            string sql = "spGetInfoThe";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@MaThe";
            valueofParameter[0] = MaThe;
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }


        /// <summary>
        /// Lấy danh sách thẻ theo khách hàng
        /// </summary>
        /// <param name="KhacHangId"></param>
        /// <returns></returns>
        public List<clsTheDTO> LayDanhSach(decimal KhacHangId)
        {
            List<clsTheDTO> CacThe = new List<clsTheDTO>();
            DataTable tabThe = LayBang(KhacHangId);
            if (tabThe == null)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < tabThe.Rows.Count; i++)
                {
                    clsTheDTO The = ChuyenDoi(tabThe.Rows[i]);
                    if (The == null)
                    {
                        return null;
                    }
                    else
                    {
                        CacThe.Add(The);
                    }
                }
                return CacThe;
            }
        }

        /// <summary>
        /// Lấy danh sách thẻ theo khách hàng
        /// </summary>
        /// <param name="KhacHangId"></param>
        /// <returns></returns>
        public List<clsTheDTO> LayDanhSachTheChuaThu(decimal KhacHangId)
        {
            List<clsTheDTO> CacThe = new List<clsTheDTO>();
            DataTable tabThe = LayBangTheChuaThu(KhacHangId);
            if (tabThe == null)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < tabThe.Rows.Count; i++)
                {
                    clsTheDTO The = ChuyenDoi(tabThe.Rows[i]);
                    if (The == null)
                    {
                        return null;
                    }
                    else
                    {
                        CacThe.Add(The);
                    }
                }
                return CacThe;
            }
        }

        /// <summary>
        /// Lấy Thong tin thẻ theo ma the
        /// </summary>
        /// <param name="KhacHangId"></param>
        /// <returns></returns>
        public clsTheDTO LayThongTinThe(string MaThe)
        {
            DataTable tabThe = LayBang(MaThe);
            if (tabThe != null && tabThe.Rows.Count > 0)
            {
                return ChuyenDoi(tabThe.Rows[0]); 
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Chuyển đổi từ datarow sang clsTheDTO
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        private clsTheDTO ChuyenDoi(DataRow row)
        {
            clsTheDTO The = new clsTheDTO();
            try
            {
                The.TheId = decimal.Parse(row["TheId"].ToString());
                The.MaThe = row["MaThe"] == null ? string.Empty : row["MaThe"].ToString(); 
                The.KhachHangId =decimal.Parse( row["KhachHangId"].ToString());
                The.NgayBatDau =DateTime.Parse( row["NgayBatDau"].ToString());
                The.NgayKetThuc = DateTime.Parse(row["NgayKetThuc"].ToString());
                The.GiaTien = double.Parse(row["GiaTien"].ToString());
                The.TienKhuyenMai = double.Parse(row["TienKhuyenMai"].ToString());
                The.ThanhTien = double.Parse(row["ThanhTien"].ToString());
                The.KhuyenMaiId = row["KhuyenMaiId"] == null ? null : (int?)int.Parse(row["KhuyenMaiId"].ToString());
                The.MoTa = row["MoTa"] == null ? string.Empty : row["MoTa"].ToString();
                The.NguoiTaoId = int.Parse(row["NguoiTaoId"].ToString());
                The.NgayTao = DateTime.Parse(row["NgayTao"].ToString());
                The.NguoiChinhSuaId = row["NguoiChinhSuaId"] == null || string.IsNullOrEmpty(row["NguoiChinhSuaId"].ToString()) ? null : (int?)int.Parse(row["NguoiChinhSuaId"].ToString());
                The.NgayChinhSua = row["NgayChinhSua"] == null || string.IsNullOrEmpty(row["NgayChinhSua"].ToString()) ? null : (DateTime?)DateTime.Parse(row["NgayChinhSua"].ToString());
                The.TrangThai = row["TrangThai"] == null ? false : Boolean.Parse(row["TrangThai"].ToString());
                return The;
            }
            catch(Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// Lấy Mã thẻ kế tiếp để phục vụ cho chức năng Insert
        /// </summary>
        public string LayMaTheMoi()
        {
            string sql = "spGetNewMaThe";
            return sqlServer.readData(sql, "@MaTheMoi");
        }

        /// <summary>
        /// Cập nhật Thẻ
        /// </summary>
        public int Sua(clsTheDTO The)
        {
            int i = -1;
            string sql = "spUpdateThe";
            string[] ParameterColection = new string[11];
            Object[] valueofParameter = new Object[11];
            ParameterColection[0] = "@TheId";
            valueofParameter[0] = The.TheId;
            ParameterColection[1] = "@NgayBatDau";
            valueofParameter[1] = The.NgayBatDau;
            ParameterColection[2] = "@NgayKetThuc";
            valueofParameter[2] = The.NgayKetThuc;
            ParameterColection[3] = "@GiaTien";
            valueofParameter[3] = The.GiaTien;
            ParameterColection[4] = "@TienKhuyenMai";
            valueofParameter[4] = The.TienKhuyenMai;
            ParameterColection[5] = "@ThanhTien";
            valueofParameter[5] = The.ThanhTien;
            ParameterColection[6] = "@KhuyenMaiId";
            valueofParameter[6] = The.KhuyenMaiId;
            ParameterColection[7] = "@MoTa";
            valueofParameter[7] = The.MoTa;
            ParameterColection[8] = "@NguoiChinhSuaId";
            valueofParameter[8] = The.NguoiChinhSuaId;
            ParameterColection[9] = "@NgayChinhSua";
            valueofParameter[9] = The.NgayChinhSua;
            ParameterColection[10] = "@TrangThai";
            valueofParameter[10] = The.TrangThai;
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        /// <summary>
        /// Thêm Thẻ
        /// </summary>
        public int Them(clsTheDTO The)
        {
            int i = -1;
            string sql = "spInsertThe";
            string[] ParameterColection = new string[10];
            Object[] valueofParameter = new Object[10];
            ParameterColection[0] = "@MaThe";
            valueofParameter[0] = The.MaThe;
            ParameterColection[1] = "@KhachHangId";
            valueofParameter[1] = The.KhachHangId;
            ParameterColection[2] = "@NgayBatDau";
            valueofParameter[2] = The.NgayBatDau;
            ParameterColection[3] = "@NgayKetThuc";
            valueofParameter[3] = The.NgayKetThuc;
            ParameterColection[4] = "@GiaTien";
            valueofParameter[4] = The.GiaTien;
            ParameterColection[5] = "@TienKhuyenMai";
            valueofParameter[5] = The.TienKhuyenMai;
            ParameterColection[6] = "@ThanhTien";
            valueofParameter[6] = The.ThanhTien;
            ParameterColection[7] = "@KhuyenMaiId";
            valueofParameter[7] = The.KhuyenMaiId;
            ParameterColection[8] = "@MoTa";
            valueofParameter[8] = The.MoTa;
            ParameterColection[9] = "@NguoiTaoId";
            valueofParameter[9] = The.NguoiTaoId;
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        /// <summary>
        /// Xóa tam thoi Thẻ
        /// </summary>
        public int XoaTamThoi(decimal TheId)
        {
            int i = -1;
            string sql = "spUpdateTrangThaiThe";
            string[] ParameterColection = new string[2];
            Object[] valueofParameter = new Object[2];
            ParameterColection[0] = "@TheId";
            valueofParameter[0] = TheId;
            ParameterColection[1] = "@TrangThai";
            valueofParameter[1] = false;
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        /// <summary>
        /// Xóa Thẻ
        /// </summary>
        public int Xoa(decimal TheId)
        {
            int i = -1;
            string sql = "spDeleteThe";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@TheId";
            valueofParameter[0] = TheId;
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

     
    }
}
